﻿using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint.Utilities;
using System.Data.SqlClient;
using DevSky.WebParts.WPToolParts;
using System.Collections.Generic;
using System.Data;
using System.Web.UI.WebControls;
using System.Linq;
using System.Collections.Specialized;

namespace DevSky.WebParts.DBConnectWP
{
    // TODO: Support multiple webpart on one page

    public partial class DBViewConfiguration : LayoutsPageBase
    {
        private string objectType = string.Empty;
        private string objectTypeSource = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                OpenerURL = Request.UrlReferrer.AbsoluteUri;
                ReadConfigFromPage();
            }
            DialogMaster dm = Master as DialogMaster;
            dm.OkButton.Click += new EventHandler(OkButton_Click);
        }

        //http://www.simple-talk.com/dotnet/.net-framework/schema-and-metadata-retrieval-using-ado.net/
        //http://blog.vuscode.com/malovicn/archive/2007/11/12/how-to-build-your-own-sql-server-explorer.aspx
        //http://www.dotnetspider.com/resources/17057-Schemas-ADO-NET.aspx
        protected void InitColumnGrid(string dbObjectName, string dbObjectType)
        {
            if (dbObjectType.Equals("SP"))
            {
                InitColumnFromSP(dbObjectName, dbObjectType);
            }
            else
            {
                InitColumnFromTabelView(dbObjectName, dbObjectType);
            }
        }

        protected void InitColumnFromTabelView(string dbObjectName, string dbObjectType)
        {
            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            DataTable columns = null;
            cnn.Open();
            try
            {
                columns = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Columns, new string[] { cnn.Database, "dbo", dbObjectName });
            }
            catch (Exception)
            { }
            finally
            {
                cnn.Close();
            }
            if (columns != null)
            {
                List<CustomDataColumn> list = new List<CustomDataColumn>();
                int pos = 0;
                foreach (DataRow col in columns.Rows)
                {
                    pos++;
                    list.Add(new CustomDataColumn() { Description = col["IS_NULLABLE"].ToString(), IsFilterable = false, Name = col["COLUMN_NAME"].ToString(), Position = pos, DataType = col["DATA_TYPE"].ToString() });
                }
                DataColumns = list;
                rptColumns.DataSource = DataColumns;
                DataBind();
            }
        }

        private void InitColumnFromSP(string dbObjectName, string dbObjectType)
        {
            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            SqlCommand cmd = new SqlCommand(dbObjectName, cnn);
            DataTable columns = new DataTable();
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cnn.Open();
                SqlCommandBuilder.DeriveParameters(cmd);
                if (cmd.Parameters != null && cmd.Parameters.Count > 0)
                    foreach (SqlParameter param in cmd.Parameters)
                        if (param.Direction == ParameterDirection.Input || param.Direction == ParameterDirection.InputOutput)
                            param.Value = -1;
                columns = new DataTable();
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(columns);
                } 
            }
            catch (Exception exc)
            {
                columns = null;
            }
            finally
            {
                cnn.Close();
            }
            if (columns != null)
            {
                List<CustomDataColumn> list = new List<CustomDataColumn>();
                int pos = 0;
                foreach (DataColumn col in columns.Columns)
                {
                    pos++;
                    list.Add(new CustomDataColumn() {IsFilterable = false, Name = col.ColumnName, Position = pos, DataType = col.DataType.Name });
                }
                DataColumns = list;
                rptColumns.DataSource = DataColumns;
                DataBind();
            }
        }

        private List<CustomDataColumn> DataColumns
        {
            get
            {
                return ViewState["DATACOLUMNS"] as List<CustomDataColumn>;
            }
            set
            {
                ViewState["DATACOLUMNS"] = value;
            }
        }

        protected void rptColumns_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            RepeaterItem item = e.Item;
            int selectedID = 0;            
            #region get control references
            Literal litDisplayName = (Literal)item.FindControl("litDisplayName");
            TextBox txtDisplayName = (TextBox)item.FindControl("txtDisplayName");

            Literal litPosition = (Literal)item.FindControl("litPosition");
            TextBox txtPosition = (TextBox)item.FindControl("txtPosition");

            Literal litTooltip = (Literal)item.FindControl("litTooltip");
            TextBox txtTooltip = (TextBox)item.FindControl("txtTooltip");

            Image imgIsDialogVisible = (Image)item.FindControl("imgIsDialogVisible");
            CheckBox chkIsDialogVisible = (CheckBox)item.FindControl("chkIsDialogVisible");

            CheckBox chkIsVisible = (CheckBox)item.FindControl("chkIsVisible");
            Image imgIsVisible = (Image)item.FindControl("imgIsVisible");

            CheckBox chkIsSortable = (CheckBox)item.FindControl("chkIsSortable");
            Image imgIsSortable = (Image)item.FindControl("imgIsSortable");

            CheckBox chkIsGroupable = (CheckBox)item.FindControl("chkIsGroupable");
            Image imgIsGroupable = (Image)item.FindControl("imgIsGroupable");            

            LinkButton btnEdit = (LinkButton)item.FindControl("btnEdit");
            LinkButton btnCancel = (LinkButton)item.FindControl("btnCancel");

            HiddenField hdfUniqueID = (HiddenField)item.FindControl("hdfUniqueID");
            Panel detailCfg = (Panel)item.FindControl("DetailCfg");

            DropDownList ddLookupObjects = (DropDownList)item.FindControl("ddLookupObjects");
            DropDownList ddLookupKeyField = (DropDownList)item.FindControl("ddLookupKeyField");
            DropDownList ddLookupValueField = (DropDownList)item.FindControl("ddLookupValueField");

            Image imgDetails = (Image)item.FindControl("imgDetails");

            TextBox txtErrorMessage = (TextBox)item.FindControl("txtErrorMessage");
            CheckBox chkIsRequired = (CheckBox)item.FindControl("chkIsRequired");
            TextBox txtRegularExpression = (TextBox)item.FindControl("txtRegularExpression");

            CheckBox chkMultipleValues = (CheckBox)item.FindControl("chkMultipleValues");
            CheckBox chkAddEmptyEntry = (CheckBox)item.FindControl("chkAddEmptyEntry");
            DropDownList ddShowAs = (DropDownList)item.FindControl("ddShowAs");

            TextBox txtInputFormat = (TextBox)item.FindControl("txtInputFormat");
            #endregion

            Int32.TryParse(hdfUniqueID.Value.ToString(), out selectedID);

            if (e.CommandName == "EditRow")
            {
                SwitchEditMode(imgDetails, ddLookupObjects, detailCfg, litDisplayName, txtDisplayName, litPosition, txtPosition, litTooltip, txtTooltip, imgIsDialogVisible, chkIsDialogVisible, chkIsVisible, imgIsVisible, chkIsSortable, imgIsSortable, chkIsGroupable, imgIsGroupable, btnEdit, btnCancel);
                CustomDataColumn dc = (from dataColumn in DataColumns where dataColumn.UniqueID == selectedID select dataColumn).FirstOrDefault();
                if (ddLookupObjects.Items.Count == 0)
                {
                    FillLookupDBObjects(ddLookupObjects);
                    if ((dc.Lookup!=null) && (!string.IsNullOrEmpty(dc.Lookup.DBObjectName)))
                        FillTableColumns(dc.Lookup.DBObjectName, ddLookupKeyField, ddLookupValueField);
                }
                if (dc.Lookup != null)
                {
                    ddLookupKeyField.SelectedValue = dc.Lookup.KeyField;
                    ddLookupValueField.SelectedValue = dc.Lookup.DisplayField;
                    ddLookupObjects.SelectedValue = dc.Lookup.DBObjectName;
                    chkMultipleValues.Checked = dc.Lookup.IsMultipleValues;
                    chkAddEmptyEntry.Checked = dc.Lookup.AddEmptyEntry;
                    ddShowAs.SelectedValue = dc.Lookup.ShowListAs;
                }

            }
            if (e.CommandName == "CancelEdit")
                SwitchReadOnlyMode(imgDetails, detailCfg, litDisplayName, txtDisplayName, litPosition, txtPosition, litTooltip, txtTooltip, imgIsDialogVisible, chkIsDialogVisible, chkIsVisible, imgIsVisible, chkIsSortable, imgIsSortable, chkIsGroupable, imgIsGroupable, btnEdit, btnCancel);
            if (e.CommandName == "SaveRow")
            {
                List<CustomDataColumn> list = DataColumns;
                if (list != null)
                {
                    if (selectedID > 0)
                    {
                        int position = 0;
                        CustomDataColumn dc = (from dataColumn in list where dataColumn.UniqueID == selectedID select dataColumn).FirstOrDefault();
                        if (dc != null)
                        {
                            Int32.TryParse(txtPosition.Text, out position);
                            dc.DisplayName = txtDisplayName.Text;
                            dc.IsDialogVisible = chkIsDialogVisible.Checked;
                            dc.IsGroupable = chkIsGroupable.Checked;
                            dc.IsFilterable = chkIsSortable.Checked;
                            dc.IsVisible = chkIsVisible.Checked;
                            dc.Position = position;
                            dc.Tooltip = txtTooltip.Text;
                            dc.ErrorMessage = txtErrorMessage.Text;
                            dc.RegularExpression = txtRegularExpression.Text;
                            dc.IsRequired = chkIsRequired.Checked;
                            dc.InputFormat = txtInputFormat.Text;

                            if ((!string.IsNullOrEmpty(ddLookupObjects.SelectedValue))
                                  && (!string.IsNullOrEmpty(ddLookupValueField.SelectedValue))
                                  && (!string.IsNullOrEmpty(ddLookupKeyField.SelectedValue))
                                  && (!string.IsNullOrEmpty(ddShowAs.SelectedValue)))
                            {
                                if (dc.Lookup == null)
                                    dc.Lookup = new Lookupefinition();
                                dc.Lookup.DBObjectName = ddLookupObjects.SelectedValue;
                                dc.Lookup.DisplayField = ddLookupValueField.SelectedValue;
                                dc.Lookup.KeyField = ddLookupKeyField.SelectedValue;
                                dc.Lookup.IsMultipleValues = chkMultipleValues.Checked;
                                dc.Lookup.AddEmptyEntry = chkAddEmptyEntry.Checked;
                                dc.Lookup.ShowListAs = ddShowAs.SelectedValue;
                            }
                            else
                                dc.Lookup = null;

                            rptColumns.DataSource = DataColumns;
                            rptColumns.DataBind();
                        }
                    }

                }
                SwitchReadOnlyMode(imgDetails, detailCfg, litDisplayName, txtDisplayName, litPosition, txtPosition, litTooltip, txtTooltip, imgIsDialogVisible, chkIsDialogVisible, chkIsVisible, imgIsVisible, chkIsSortable, imgIsSortable, chkIsGroupable, imgIsGroupable, btnEdit, btnCancel);
            }            
        }

        protected void ddLookupObjects_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddList = (sender as DropDownList);            
            if (ddList != null)
            {
                Panel panel = (Panel)ddList.Parent.FindControl("DetailCfg");
                DropDownList ddLookupKeyField = (DropDownList)ddList.Parent.FindControl("ddLookupKeyField");
                DropDownList ddLookupValueField = (DropDownList)ddList.Parent.FindControl("ddLookupValueField");
                ddLookupKeyField.Items.Clear();
                ddLookupValueField.Items.Clear();
                if (!string.IsNullOrEmpty(ddList.SelectedValue))
                    FillTableColumns(ddList.SelectedValue, ddLookupKeyField, ddLookupValueField);
                panel.Style["display"] = "block";
            }
        }

        private void SwitchEditMode(Image imgDetails, DropDownList ddLookupObjects, Panel detailConfig, Literal litDisplayName, TextBox txtDisplayName, Literal litPosition, TextBox txtPosition, Literal litTooltip, TextBox txtTooltip, Image imgIsDialogVisible, CheckBox chkIsDialogVisible, CheckBox chkIsVisible, Image imgIsVisible, CheckBox chkIsSortable, Image imgIsSortable, CheckBox chkIsGroupable, Image imgIsGroupable, LinkButton btnEdit, LinkButton btnCancel)
        {
            litDisplayName.Visible = false;
            imgIsDialogVisible.Visible = false;
            litTooltip.Visible = false;
            imgIsVisible.Visible = false;
            imgIsSortable.Visible = false;
            imgIsGroupable.Visible = false;
            litPosition.Visible = false;
            txtDisplayName.Visible = true;
            chkIsDialogVisible.Visible = true;
            txtTooltip.Visible = true;
            chkIsVisible.Visible = true;
            chkIsSortable.Visible = true;
            txtPosition.Visible = true;
            chkIsGroupable.Visible = true;
            
            btnEdit.Text = "Save";
            btnEdit.CommandName = "SaveRow";
            btnCancel.Visible = true;

            chkIsDialogVisible.Attributes.Remove("onclick");
            chkIsDialogVisible.Attributes.Add("onclick", string.Format("DetailDialogConfig(this,'{0}','{1}')", detailConfig.ClientID, imgDetails.ClientID));
            detailConfig.Style["display"] = chkIsDialogVisible.Checked?"block":"none";

            imgDetails.Visible = true;
            imgDetails.Attributes.Remove("onclick");
            imgDetails.Attributes.Add("onclick", string.Format("OpenMoreSettings(this,'{0}')", detailConfig.ClientID));
            imgDetails.ImageUrl = chkIsDialogVisible.Checked ? "/_layouts/images/DevSkySolutions/arrow_up.png" : "/_layouts/images/DevSkySolutions/arrow_down.png";
            
        }

        private void SwitchReadOnlyMode(Image imgDetails, Panel detailConfig, Literal litDisplayName, TextBox txtDisplayName, Literal litPosition, TextBox txtPosition, Literal litTooltip, TextBox txtTooltip, Image imgIsDialogVisible, CheckBox chkIsDialogVisible, CheckBox chkIsVisible, Image imgIsVisible, CheckBox chkIsSortable, Image imgIsSortable, CheckBox chkIsGroupable, Image imgIsGroupable, LinkButton btnEdit, LinkButton btnCancel)
        {
            litDisplayName.Visible = true;
            imgIsDialogVisible.Visible = true;
            litTooltip.Visible = true;
            imgIsVisible.Visible = true;
            imgIsSortable.Visible = true;
            imgIsGroupable.Visible = true;
            litPosition.Visible = true;
            txtDisplayName.Visible = false;
            chkIsDialogVisible.Visible = false;
            txtTooltip.Visible = false;
            chkIsVisible.Visible = false;
            chkIsSortable.Visible = false;
            txtPosition.Visible = false;
            chkIsGroupable.Visible = false;

            btnEdit.CommandName = "EditRow";
            btnEdit.Text = "Edit";
            btnCancel.Visible = false;

            detailConfig.Style["display"] = "none";
            imgDetails.Visible = false;
        }

        private string CustomerConnectionString
        {
            get
            {
                if (ViewState["CustomerConnectionString"] == null)
                    return string.Empty;
                return ViewState["CustomerConnectionString"].ToString();
            }
            set
            {
                ViewState["CustomerConnectionString"] = value;
            }
        }

        private string OpenerURL
        {
            get
            {
                if (ViewState["OpenerURL"] == null)
                    return string.Empty;
                return ViewState["OpenerURL"].ToString();
            }
            set
            {
                ViewState["OpenerURL"] = value;
            }
        }

        void OkButton_Click(object sender, EventArgs e)
        {
            SaveConfiguration();
            Tools.Show("Configuration saved", true);
        }

        private void SaveConfiguration()
        {
            Configuration cfg = new Configuration(Guid.Empty, OpenerURL);
            cfg.ShowCustomLinks = chkCustomLinks.Checked;
            cfg.ShowDeleteMenuEntry = chkDelete.Checked;
            cfg.ShowEditLinkMenuEntry = chkEditMode.Checked;
            cfg.ShowNewMenuEntry = chkNew.Checked;
            cfg.ShowReadOnlyMenuEntry = chkReadMode.Checked;
            cfg.DialogTitle = txtDialogTitle.Text;
            foreach (CustomDataColumn item in DataColumns)
                item.IsUniqueColumn = false;
            if (!string.IsNullOrEmpty(ddIDColumn.SelectedValue))
            {
                CustomDataColumn dc = (from dataColumn in DataColumns where dataColumn.Name == ddIDColumn.SelectedValue select dataColumn).FirstOrDefault();
                if (dc != null)
                    dc.IsUniqueColumn = true;
            }
            cfg.CustomDataColumns = DataColumns;
            cfg.SaveConfiguration();
        }

        //http://www.sharepointsecurity.com/sharepoint/sharepoint-development/open-configuration-panel-from-webpart-toolpane/
        private void ReadConfigFromPage()
        {
            Configuration cfg = new Configuration(Guid.Empty, OpenerURL);
            if (cfg.ReadConfigFromPage())
            {
                CustomerConnectionString = cfg.DBConnectString;
                objectTypeSource = cfg.DBObjectName;
                objectType = cfg.DBObjectType;
                chkCustomLinks.Checked = cfg.ShowCustomLinks;
                chkDelete.Checked = cfg.ShowDeleteMenuEntry;
                chkEditMode.Checked = cfg.ShowEditLinkMenuEntry;
                chkNew.Checked = cfg.ShowNewMenuEntry;
                chkReadMode.Checked = cfg.ShowReadOnlyMenuEntry;
                txtDialogTitle.Text = cfg.DialogTitle;
                DataColumns = cfg.CustomDataColumns;
                if (DataColumns != null)
                {
                    rptColumns.DataSource = DataColumns;
                    DataBind();
                }
                else
                    InitColumnGrid(objectTypeSource, objectType);
                ddIDColumn.Items.Clear();
                foreach (CustomDataColumn column in DataColumns)
                    ddIDColumn.Items.Add(new ListItem(column.Name, column.Name));
                CustomDataColumn dc = (from dataColumn in DataColumns where dataColumn.IsUniqueColumn select dataColumn).FirstOrDefault();
                if (dc != null)
                    ddIDColumn.SelectedValue = dc.Name;
            }
        }


        #region LOOKUP FIELDS

        private List<ListItem> dbLookupObjects = null;
        private void FillLookupDBObjects(DropDownList ddList)
        {
            if (dbLookupObjects == null)
            {
                SqlConnection cnn = new SqlConnection(CustomerConnectionString);
                DataTable dtObjects = null;
                try
                {
                    cnn.Open();
                    dtObjects = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" });
                    dtObjects.Merge(cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Views));
                }
                catch (Exception)
                { }
                finally
                {
                    cnn.Close();
                }
                if (dtObjects != null)
                {
                    dbLookupObjects = new List<ListItem>();
                    foreach (System.Data.DataRow table in dtObjects.Rows)
                        dbLookupObjects.Add(new ListItem(table["table_name"].ToString(), table["table_name"].ToString()));
                }
            }
            if (dbLookupObjects != null)
            {
                ddList.Items.Clear();
                ddList.Items.Add(new ListItem("Please choose one entry ...", string.Empty));
                foreach (ListItem item in dbLookupObjects)
                    ddList.Items.Add(item);
            }
        }

        protected void FillTableColumns(string dbObjectName, DropDownList ddList, DropDownList ddList2)
        {
            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            DataTable columns = null;
            cnn.Open();
            try
            {
                columns = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Columns, new string[] { cnn.Database, "dbo", dbObjectName });
            }
            catch (Exception)
            { }
            finally
            {
                cnn.Close();
            }
            if (columns != null)
            {
                ddList.Items.Clear();
                ddList.Items.Add(new ListItem("Please choose one entry ...", string.Empty));
                ddList2.Items.Clear();
                ddList2.Items.Add(new ListItem("Please choose one entry ...", string.Empty));
                foreach (DataRow col in columns.Rows)
                {
                    ddList.Items.Add(new ListItem(col["COLUMN_NAME"].ToString(), col["COLUMN_NAME"].ToString()));
                    ddList2.Items.Add(new ListItem(col["COLUMN_NAME"].ToString(), col["COLUMN_NAME"].ToString()));
                }
            }
        }

        #endregion
    }
}
